/*
 * Copyright (c)  2008 Sebastien Tremblay
 *
 * id3-tag-builder - classes.db.SqliteMusicDb.java
 *
 * All source and documentation is copyrighted by Sebastien Tremblay
 * (seb at ryders dot net) and made available under the Apache License 2.0.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * $Id: SqliteMusicDb.java 33 2010-04-02 07:47:55Z ryders $
 */
package com.empireone.id3tagbuilder.classes.db;

import com.empireone.id3tagbuilder.classes.Format;
import com.empireone.id3tagbuilder.classes.Path;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sebastien Tremblay
 */
public class SqliteMusicDb implements MusicDb {

    private File db;
    private Connection conn;
    private String version;

    /**
     * 
     * @param uri
     */
    @Override
    public void setUri(String uri) {
        if (!uri.contains(System.getProperty("file.separator"))) {
            uri = System.getProperty("user.dir") + System.getProperty("file.separator") + uri;
        }
        this.db = new File(uri + (uri.endsWith(".db") ? "" : ".db"));
    }

    /**
     *
     * @throws MusicDbException
     */
    @Override
    public void initialise() throws MusicDbException {

        if (db == null) {
            throw new MusicDbException("Cannot find - " + db.getPath());
        } else if (!canWrite()) {
            throw new MusicDbException("Doesn't exist and cannot create - " + db.getPath());
        }

        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:" + db.getPath());
        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }

        if (!checkSchema()) {
            throw new MusicDbException("MusicDB Corruption Risk");
        }
    }

    /**
     *
     */
    @Override
    public void terminate() {

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    /**
     *
     * @return
     * @throws MusicDbException
     */
    @Override
    public boolean canWrite() throws MusicDbException {
        try {
            return db != null && (db.exists() || db.createNewFile());
        } catch (IOException ex) {
            return false;
        }
    }

    /**
     *
     */
    @Override
    public void beginSave() {
        //throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     *
     * @param album
     * @throws MusicDbException
     */
    @Override
    public void saveAlbum(MusicAlbum album) throws MusicDbException {
        try {
            ResultSet rs;

            int artist_id = 0, label_id = 0, album_id = 0;
            String sqls = null, sqli = null;

            if (album.getArtist() != null) {
                sqls = "SELECT * FROM artists WHERE name = '" + album.getArtist() + "'";
                sqli = "INSERT INTO artists (name) VALUES ('" + album.getArtist() + "')";

                artist_id = getId(sqls, sqli);
            }


            if (album.getRecordLabel() != null) {
                sqls = "SELECT * FROM labels WHERE name = '" + album.getRecordLabel() + "'";
                sqli = "INSERT INTO labels (name) VALUES ('" + album.getRecordLabel() + "')";

                label_id = getId(sqls, sqli);
            }

            sqls = "SELECT * FROM albums WHERE artist_id = " + (artist_id > 0 ? artist_id : "NULL") + " AND year = " + album.getYear() + " AND name = '" + album.getName() + "'";
            sqli = "INSERT INTO albums (artist_id, name, year) VALUES (" + artist_id + ", '" + album.getName() + "', " + album.getYear() + ")";

            album_id = getId(sqls, sqli);

            Statement st = conn.createStatement();
            st.execute("DELETE FROM tracks WHERE album_id = " + album_id);
            PreparedStatement pst = conn.prepareStatement(
                    "INSERT INTO tracks (name, album_id, artist_id, year, no, disc) VALUES (?, ?, ?, ?, ?, ?);");


            for (MusicTrack t : album.getTracks()) {

                pst.setString(1, t.getTitle());
                pst.setInt(2, album_id);
                pst.setString(3, "NULL");
                pst.setInt(4, t.getYear());
                pst.setInt(5, t.getTrackNo());
                pst.setInt(6, t.getDiscNo());

                pst.addBatch();
            }
            conn.setAutoCommit(false);
            pst.executeBatch();
            conn.setAutoCommit(true);

            pst.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    private int getId(String sqls, String sqli) {
        int res = -1;
        Statement st;

        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery(sqls);

            if (!rs.next()) {
                st.executeUpdate(sqli);
                rs = st.executeQuery(sqls);
                rs.next();
            }

            res = rs.getInt("id");
            rs.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    /**
     *
     */
    @Override
    public void endSave() {
        //throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     *
     * @param format
     * @return
     * @throws MusicDbException
     */
    @Override
    public Path[] getByFormat(Format format) throws MusicDbException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    protected boolean checkSchema() {

        Statement st;
        boolean res = false;

        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT name FROM sqlite_master WHERE name='_props'");

            if (rs.next()) {
                rs = st.executeQuery("SELECT * FROM _props WHERE name = 'version'");
                if (rs.next()) {
                    res = upgradeSchema(rs.getString("value"));
                }

            } else {
                res = createSchema();
            }

            rs.close();
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    protected boolean upgradeSchema(String fromVersion) {
        Statement st;
        try {
            st = conn.createStatement();

            if (fromVersion.equals("0.1")) {
                //st.executeUpdate("UPDATE _props SET value = '0.1' WHERE name = 'version'");
                //return upgradeSchema("0.2");
            }

        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }

        return true;

    }

    protected boolean createSchema() {
        Statement st;
        try {
            st = conn.createStatement();
            st.executeUpdate("CREATE TABLE _props (name TEXT, value TEXT)");

            if (version.equals("0.1")) {
                st.executeUpdate("CREATE TABLE albums (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, label_id INTEGER, artist_id INTEGER, path INTEGER, format INTEGER, year INT, cover BLOB)");
                st.executeUpdate("CREATE TABLE artists (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
                st.executeUpdate("CREATE TABLE labels (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
                st.executeUpdate("CREATE TABLE tracks (name TEXT, album_id INTEGER, artist_id INTEGER, year INTEGER, no INTEGER, disc INTEGER)");

                st.executeUpdate("INSERT INTO _props VALUES ('version','0.1')");
            }

            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(SqliteMusicDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    @Override
    public void setVersion(String version) {
        this.version = version;
    }
}
